<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>explain | Manor Farm Mysql</title>
    <meta name="generator" content="VuePress 1.7.1">
    <link rel="icon" href="/manor-farm-mysql/pgmanor_mysql.svg">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.css">
    <script language="javascript" type="text/javascript" src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="/manor-farm-mysql/js/MouseClickEffect.js"></script>
    <script language="javascript" type="text/javascript" src="/manor-farm-mysql/js/pgmanor-self.js"></script>
    <meta name="description" content="Mysql学习手册">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/manor-farm-mysql/assets/css/0.styles.7bfce8db.css" as="style"><link rel="preload" href="/manor-farm-mysql/assets/js/app.bbd54b43.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/3.33fa8ee0.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/1.e501e4e7.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/21.4fd4cbc1.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/8.73e907b5.js" as="script"><link rel="prefetch" href="/manor-farm-mysql/assets/js/10.31e485b8.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/11.bd87e4cf.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/12.0fe1282f.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/13.f1de335a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/14.4a84a4ee.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/15.bc190a07.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/16.3f42a3ab.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/17.f1cad67c.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/18.e8063fee.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/19.068739ed.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/20.2b248747.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/22.2683cc54.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/23.2e39bbe5.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/24.560a4164.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/25.26555323.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/26.8b7e1943.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/27.0f4ac9a1.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/28.f89ec4c0.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/29.71c751c3.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/30.ad576991.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/31.2746a45e.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/32.b62e21d8.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/33.ae9d6c1c.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/34.53969cd0.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/35.3e1a8a01.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/36.eb74300d.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/37.16568451.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/38.9a3bc894.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/39.7aab5a75.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/4.c231685d.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/40.cd44603a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/41.64d0c11a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/42.3fb5d657.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/43.d4f19236.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/44.6abd5f31.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/45.eaf7b761.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/46.d891856e.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/5.0292b513.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/6.df2ba939.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/7.3b1acb14.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/9.5220a7da.js">
    <link rel="stylesheet" href="/manor-farm-mysql/assets/css/0.styles.7bfce8db.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-2d5f533b><div data-v-2d5f533b><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-2d5f533b data-v-2d5f533b><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-59a94c5a data-v-2d5f533b data-v-2d5f533b><h3 class="title" style="display:none;" data-v-59a94c5a data-v-59a94c5a>Manor Farm Mysql</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-59a94c5a data-v-59a94c5a><input type="password" value="" data-v-59a94c5a> <span data-v-59a94c5a>已加密!</span> <button data-v-59a94c5a>OK</button></label> <div class="footer" style="display:none;" data-v-59a94c5a data-v-59a94c5a><span data-v-59a94c5a><i class="iconfont reco-eye" data-v-59a94c5a></i> <a target="blank" href="https://pgmanor.gitee.io/blog/" data-v-59a94c5a>密钥</a></span> <span data-v-59a94c5a><i class="iconfont reco-theme" data-v-59a94c5a></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-59a94c5a>vuePress-theme-reco</a></span> <span data-v-59a94c5a><i class="iconfont reco-copyright" data-v-59a94c5a></i> <a data-v-59a94c5a><span data-v-59a94c5a>pgmanor</span>
  
<!---->
2021
</a></span></div></div> <div class="hide" data-v-2d5f533b><header class="navbar" data-v-2d5f533b><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/manor-farm-mysql/" class="home-link router-link-active"><img src="/manor-farm-mysql/pgmanor_mysql.svg" alt="Manor Farm Mysql" class="logo"> <span class="site-name">Manor Farm Mysql</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/manor-farm-mysql/zh/guide/" class="nav-link router-link-active"><i class="iconfont reco-api"></i>
  指南
</a></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/messageBoard.html" class="nav-link"><i class="iconfont reco-suggestion"></i>
  留言版
</a></div><div class="nav-item"><a href="https://pgmanor.gitee.io/blog/2021/01/10/secret-farm-mysql/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-eye"></i>
  文章密钥
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-qq"></i>
      联系
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>QQ在线</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://wpa.qq.com/msgrd?v=3&amp;uin=2307546059&amp;site=qq&amp;menu=yes" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>其他方式</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/manor-farm-mysql/zh/fix/contactUs.html" class="nav-link"><i class="iconfont undefined"></i>
  更多
</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-other"></i>
      友链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://pgmanor.gitee.io/blog/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://blog.csdn.net/u011622109" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-csd"></i>
  csdn
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://www.iworkh.com" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  iworkh
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>Vue</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://cn.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  Vue
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://v1.vuepress.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  VuePress
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://vuepress-theme-reco.recoluan.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  vuepress-theme-reco
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li></ul></div></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/donate.html" class="nav-link"><i class="iconfont reco-Coding"></i>
  捐赠
</a></div><div class="nav-item"><a href="https://gitee.com/pgmanor/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-2d5f533b></div> <aside class="sidebar" data-v-2d5f533b><div class="personal-info-wrapper" data-v-ca798c94 data-v-2d5f533b><img src="/manor-farm-mysql/pgmanor_mysql.svg" alt="author-avatar" class="personal-img" data-v-ca798c94> <h3 class="name" data-v-ca798c94>
    pgmanor
  </h3> <div class="num" data-v-ca798c94><div data-v-ca798c94><h3 data-v-ca798c94>30</h3> <h6 data-v-ca798c94>文章</h6></div> <div data-v-ca798c94><h3 data-v-ca798c94>26</h3> <h6 data-v-ca798c94>标签</h6></div></div> <hr data-v-ca798c94></div> <nav class="nav-links"><div class="nav-item"><a href="/manor-farm-mysql/zh/guide/" class="nav-link router-link-active"><i class="iconfont reco-api"></i>
  指南
</a></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/messageBoard.html" class="nav-link"><i class="iconfont reco-suggestion"></i>
  留言版
</a></div><div class="nav-item"><a href="https://pgmanor.gitee.io/blog/2021/01/10/secret-farm-mysql/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-eye"></i>
  文章密钥
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-qq"></i>
      联系
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>QQ在线</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://wpa.qq.com/msgrd?v=3&amp;uin=2307546059&amp;site=qq&amp;menu=yes" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>其他方式</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/manor-farm-mysql/zh/fix/contactUs.html" class="nav-link"><i class="iconfont undefined"></i>
  更多
</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-other"></i>
      友链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://pgmanor.gitee.io/blog/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://blog.csdn.net/u011622109" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-csd"></i>
  csdn
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://www.iworkh.com" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  iworkh
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>Vue</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://cn.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  Vue
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://v1.vuepress.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  VuePress
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://vuepress-theme-reco.recoluan.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  vuepress-theme-reco
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li></ul></div></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/donate.html" class="nav-link"><i class="iconfont reco-Coding"></i>
  捐赠
</a></div><div class="nav-item"><a href="https://gitee.com/pgmanor/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav> <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>初入尘世</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/manor-farm-mysql/zh/guide/essentials/e-01-install.html" class="sidebar-link">安装</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-02-ddl-dcl.html" class="sidebar-link">DDL语句</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-03-dml.html" class="sidebar-link">DML语句</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-04-sort.html" class="sidebar-link">sort排序</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-05-group.html" class="sidebar-link">group分组</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-06-union.html" class="sidebar-link">union合并</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-07-join.html" class="sidebar-link">join</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-08-transaction.html" class="sidebar-link">事务</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-09-view.html" class="sidebar-link">视图</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-10-procedure.html" class="sidebar-link">存储过程</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-11-function.html" class="sidebar-link">函数</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-12-index.html" class="sidebar-link">索引</a></li><li><a href="/manor-farm-mysql/zh/guide/essentials/e-13-devops.html" class="sidebar-link">运维</a></li></ul></section></li><li><section class="sidebar-group depth-0"><p class="sidebar-heading"><span>千锤百炼</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/manor-farm-mysql/zh/guide/advanced/a-01-introduction.html" class="sidebar-link">本章介绍</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/a-02-prepare-data.html" class="sidebar-link">数据准备</a></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><a href="/manor-farm-mysql/zh/guide/" class="sidebar-heading clickable router-link-active"><span>原理</span> <span class="arrow right"></span></a> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><a href="/manor-farm-mysql/zh/guide/" class="sidebar-heading clickable router-link-active open"><span>优化</span> <span class="arrow down"></span></a> <ul class="sidebar-links sidebar-group-items"><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-01-slow-query.html" class="sidebar-link">慢查询</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-02-mysqldumpslow.html" class="sidebar-link">mysqldumpslow</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-03-explain.html" aria-current="page" class="active sidebar-link">explain</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-04-explain-select-type.html" class="sidebar-link">explain之select_type</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-05-explain-type.html" class="sidebar-link">explain之type</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-06-explain-extra.html" class="sidebar-link">explain之extra</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-07-optimization.html" class="sidebar-link">优化思路和手段</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-08-optimization-join.html" class="sidebar-link">优化之join</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-09-optimization-orderby.html" class="sidebar-link">优化之排序</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-10-optimization-groupby.html" class="sidebar-link">优化之分组</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-11-optimization-struct.html" class="sidebar-link">结构优化</a></li></ul></section></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>百战成神</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-59a94c5a data-v-2d5f533b><h3 class="title" style="display:none;" data-v-59a94c5a data-v-59a94c5a>explain</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-59a94c5a data-v-59a94c5a><input type="password" value="" data-v-59a94c5a> <span data-v-59a94c5a>已加密!</span> <button data-v-59a94c5a>OK</button></label> <div class="footer" style="display:none;" data-v-59a94c5a data-v-59a94c5a><span data-v-59a94c5a><i class="iconfont reco-eye" data-v-59a94c5a></i> <a target="blank" href="https://pgmanor.gitee.io/blog/" data-v-59a94c5a>密钥</a></span> <span data-v-59a94c5a><i class="iconfont reco-theme" data-v-59a94c5a></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-59a94c5a>vuePress-theme-reco</a></span> <span data-v-59a94c5a><i class="iconfont reco-copyright" data-v-59a94c5a></i> <a data-v-59a94c5a><span data-v-59a94c5a>pgmanor</span>
  
<!---->
2021
</a></span></div></div> <div data-v-2d5f533b><main class="page"><div class="page-title" style="display:none;"><h1 class="title">explain</h1> <div data-v-3b7f5bdf><i class="iconfont reco-account" data-v-3b7f5bdf><span data-v-3b7f5bdf>pgmanor</span></i> <i class="iconfont reco-date" data-v-3b7f5bdf><span data-v-3b7f5bdf>2021-01-02</span></i> <i class="iconfont reco-eye" data-v-3b7f5bdf><span id="/manor-farm-mysql/zh/guide/advanced/optimization/a-03-explain.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-3b7f5bdf><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-3b7f5bdf><span class="tag-item" data-v-3b7f5bdf>explain</span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><p>本文主要介绍下explain</p> <p>通过explian可以查看sql的执行计划，分析sql语句和表结构的性能瓶颈。</p> <blockquote><p>explain的使用十分简单，通过在查询语句前面加一个explain关键字即可。</p></blockquote> <ul><li><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html" target="_blank" rel="noopener noreferrer">mysql官网explian文档<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <div id="boxx" data-v-f4ca0dac><div data-v-f4ca0dac><p v-if="true" class="custom-block-title" data-v-f4ca0dac></p> <p v-if="true" data-v-f4ca0dac></p></div></div> <h2 id="_1-explain字段">1.explain字段</h2> <p>explain查看执行计划，共有以下信息返回</p> <ul><li><strong>id</strong>: 序号，表示执行顺序</li> <li><strong>select_type</strong>: 查询类型</li> <li><strong>table</strong>: 访问的表</li> <li><strong>partitions</strong>: 分区</li> <li><strong>type</strong>: 访问的类型</li> <li><strong>possible_keys</strong>: 可能使用到的索引，实际不一定使用</li> <li><strong>key</strong>: 实际使用的索引，为null表示没有使用索引</li> <li><strong>key_len</strong>: 索引使用的字节数</li> <li><strong>ref</strong>: 列与索引的比较</li> <li><strong>rows</strong>: 根据统计信息，估算出可能需要读取的行数</li> <li><strong>filtered</strong>: 查询的表行数/表的百分比</li> <li><strong>extra</strong>: 一些重要的额外信息</li></ul> <p><strong>查询sql根据复杂程度分以下几个类别</strong></p> <ul><li>简单</li> <li>复杂
<ul><li>简单子查询 (select、where中有子查询)</li> <li>派生表 (from中有子查询)</li> <li>union查询</li></ul></li></ul> <h2 id="_2-id">2.id</h2> <p>SELECT识别符，每个select语句都会自动分配的一个唯一标识符。SQL执行的顺序的标识。遵循以下原则</p> <ul><li><strong>id从大到小的执行</strong></li> <li><strong>id相同时，执行顺序由上至下</strong></li> <li><strong>id列为null表示为结果集，不需要使用这个语句来查询</strong></li></ul> <h3 id="_2-1-id相同">2-1.id相同</h3> <p>查询演员id为1的电影信息</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film f <span class="token keyword">where</span> f<span class="token punctuation">.</span>film_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> film_Id <span class="token keyword">from</span> film_actor fa <span class="token keyword">where</span> fa<span class="token punctuation">.</span>actor_id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token operator">-</span> 或者
<span class="token keyword">explain</span> <span class="token keyword">select</span> f<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">from</span> film f<span class="token punctuation">,</span> film_actor fa <span class="token keyword">where</span> fa<span class="token punctuation">.</span>film_id <span class="token operator">=</span> f<span class="token punctuation">.</span>film_id <span class="token operator">and</span> fa<span class="token punctuation">.</span>actor_id <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>fa</td> <td></td> <td>ref</td> <td>PRIMARY,idx_fk_film_id</td> <td>PRIMARY</td> <td>2</td> <td>const</td> <td>19</td> <td>100.0</td> <td>Using index</td></tr> <tr><td>1</td> <td>SIMPLE</td> <td>f</td> <td></td> <td>eq_ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td>sakila.fa.film_id</td> <td>1</td> <td>100.0</td> <td></td></tr></tbody></table> <blockquote><p>id相同，由上至下，先查fa(film_actor)， 再查f(film)表</p></blockquote> <h3 id="_2-2-id不同">2-2.id不同</h3> <p>查询演员id为1和2的电影信息</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film f <span class="token keyword">where</span> f<span class="token punctuation">.</span>film_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> film_Id <span class="token keyword">from</span> film_actor fa <span class="token keyword">where</span> fa<span class="token punctuation">.</span>actor_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td><code>&lt;subquery2&gt;</code></td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>100.0</td> <td></td></tr> <tr><td>1</td> <td>SIMPLE</td> <td>f</td> <td></td> <td>eq_ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td><code>&lt;subquery2&gt;</code>.film_Id</td> <td>1</td> <td>100.0</td> <td></td></tr> <tr><td>2</td> <td>MATERIALIZED</td> <td>fa</td> <td></td> <td>range</td> <td>PRIMARY,idx_fk_film_id</td> <td>PRIMARY</td> <td>2</td> <td></td> <td>44</td> <td>100.0</td> <td>Using where; Using index</td></tr></tbody></table> <p><strong>函数</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> f<span class="token punctuation">.</span>title <span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token keyword">select</span> ceil<span class="token punctuation">(</span>rand<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">*</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">from</span> dual<span class="token punctuation">)</span>  <span class="token keyword">as</span> randnum <span class="token keyword">from</span> film f<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>PRIMARY</td> <td>f</td> <td></td> <td>index</td> <td></td> <td>idx_title</td> <td>514</td> <td></td> <td>1000</td> <td>100.0</td> <td>Using index</td></tr> <tr><td>2</td> <td>UNCACHEABLE SUBQUERY</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>No tables used</td></tr></tbody></table> <h3 id="_2-3-null">2-3.null</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> film_id<span class="token punctuation">,</span> title<span class="token punctuation">,</span> release_year <span class="token keyword">from</span> film <span class="token keyword">where</span> rental_rate <span class="token operator">=</span> <span class="token number">4.99</span> 
<span class="token keyword">union</span> <span class="token keyword">select</span> film_id<span class="token punctuation">,</span> title<span class="token punctuation">,</span> release_year <span class="token keyword">from</span> film <span class="token keyword">where</span> rental_rate <span class="token operator">=</span> <span class="token number">3.99</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>PRIMARY</td> <td>film</td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td>1000</td> <td>10.0</td> <td>Using where</td></tr> <tr><td>2</td> <td>UNION</td> <td>film</td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td>1000</td> <td>10.0</td> <td>Using where</td></tr> <tr><td></td> <td>UNION RESULT</td> <td><code>&lt;union1,2&gt;</code></td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>Using temporary</td></tr></tbody></table> <blockquote><p><code>&lt;union1,2&gt;</code>表示依赖id为1和2的</p></blockquote> <h2 id="_3-select-type">3.select_type</h2> <ul><li><strong>simple</strong>: 简单查询。查询不包含子查询和union</li> <li><strong>primary</strong>：复杂查询(子查询、union查询)中最外层的select</li> <li><strong>subquery</strong>: 除了from子句中包含的子查询外，其它地方出现的子查询都可能是subquery</li> <li><strong>derived</strong>：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）</li> <li><strong>union</strong>：在union中的第二个和随后的select</li> <li><strong>union result</strong>：从union临时表检索结果的select</li> <li><strong>dependent subquery</strong>: 子查询的结果受到外层的影响</li> <li><strong>dependent union</strong>: UNION中的第二个或后面的SELECT语句，取决于外面的查询</li> <li><strong>materialized</strong>: 物化子查询</li> <li><strong>uncacheable subquery</strong>:  子查询，结果无法缓存，必须针对外部查询的每一行重新评估</li> <li><strong>uncacheable union</strong>: 属于UNCACHEABLE SUBQUERY的第二个或后面的查询</li></ul> <p><code>select_type</code>细节比较多，所以单独一节来介绍，具体请查看 <a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-04-explain-select-type.html">explain之select_type</a></p> <h2 id="_4-table">4.table</h2> <p><strong>table</strong>: 访问的表，可能出现的值</p> <ul><li><code>表名</code>: 这不用了多讲解，从一个表中查询</li> <li><code>&lt;unionM,N&gt;</code>: 有union操作的时候，<code>UNION RESULT</code>合并的时候，M和N代码依赖的执行计划id序号</li> <li><code>&lt;subqueryN&gt;</code>: 当简单子查询的时候，会出现。表示临时表，N表示执行计划中id</li> <li><code>derived</code>: from子查询时候，会出现</li> <li><code>空</code>: 一些函数，不需要通过表获取数据</li></ul> <p><strong>示例1</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film <span class="token keyword">where</span> film_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> film_Id <span class="token keyword">from</span> film_actor <span class="token keyword">where</span> actor_id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td><code>&lt;subquery2&gt;</code></td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>100.0</td> <td></td></tr> <tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>eq_ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td><code>&lt;subquery2&gt;</code>.film_Id</td> <td>1</td> <td>100.0</td> <td></td></tr> <tr><td>2</td> <td>MATERIALIZED</td> <td>film_actor</td> <td></td> <td>range</td> <td>PRIMARY,idx_fk_film_id</td> <td>PRIMARY</td> <td>2</td> <td></td> <td>44</td> <td>100.0</td> <td>Using where; Using index</td></tr></tbody></table> <p><strong>示例2</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> film_id<span class="token punctuation">,</span> title<span class="token punctuation">,</span> release_year <span class="token keyword">from</span> film <span class="token keyword">where</span> rental_rate <span class="token operator">=</span> <span class="token number">4.99</span> 
<span class="token keyword">union</span> <span class="token keyword">select</span> film_id<span class="token punctuation">,</span> title<span class="token punctuation">,</span> release_year <span class="token keyword">from</span> film <span class="token keyword">where</span> rental_rate <span class="token operator">=</span> <span class="token number">3.99</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>PRIMARY</td> <td>film</td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td>1000</td> <td>10.0</td> <td>Using where</td></tr> <tr><td>2</td> <td>UNION</td> <td>film</td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td>1000</td> <td>10.0</td> <td>Using where</td></tr> <tr><td></td> <td>UNION RESULT</td> <td><code>&lt;union1,2&gt;</code></td> <td></td> <td>ALL</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>Using temporary</td></tr></tbody></table> <p><strong>示例3</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> tn<span class="token punctuation">.</span>randnum  <span class="token keyword">from</span> <span class="token punctuation">(</span><span class="token keyword">select</span> rand<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">*</span><span class="token number">10</span>  <span class="token keyword">as</span> randnum <span class="token keyword">from</span> dual<span class="token punctuation">)</span> <span class="token keyword">as</span> tn<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>PRIMARY</td> <td><code>&lt;derived2&gt;</code></td> <td></td> <td>system</td> <td></td> <td></td> <td></td> <td></td> <td>1</td> <td>100.0</td> <td></td></tr> <tr><td>2</td> <td>DERIVED</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td>No tables used</td></tr></tbody></table> <blockquote><p><code>&lt;derived2&gt;</code>表示依赖于id为1的，而且是派生表</p></blockquote> <h2 id="_5-partitions">5.partitions</h2> <p><strong>paritions</strong>对于分区表才会有效，未分区表则未空。</p> <p><strong>分区按照维度可以分为水平和垂直</strong></p> <ul><li>水平分区的模式
<ul><li>Range（范围）: 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。</li> <li>Hash（哈希）:这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。</li> <li>Key（键值）: Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。</li> <li>List（预定义列表）: 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。</li> <li>Composite（复合模式）: 以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。</li></ul></li> <li>垂直分区（按列分）：
<blockquote><p>举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。</p></blockquote></li></ul> <p>如果对分区表有兴趣的，可以阅读下这篇文章<a href="https://www.cnblogs.com/mzhaox/p/11201715.html" target="_blank" rel="noopener noreferrer">深入解析MySQL分区Partition功能<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>，这就不多介绍了。</p> <p><strong>分区建表语句</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> p_student <span class="token punctuation">(</span>
 id <span class="token keyword">int</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'id'</span><span class="token punctuation">,</span>
 name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token keyword">default</span> <span class="token string">''</span> <span class="token keyword">COMMENT</span> <span class="token string">'姓名'</span><span class="token punctuation">,</span>
 grade <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">default</span> <span class="token number">1</span> <span class="token keyword">COMMENT</span> <span class="token string">'年级'</span><span class="token punctuation">,</span>
 <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4 <span class="token keyword">COLLATE</span><span class="token operator">=</span>utf8mb4_general_ci
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> range <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token punctuation">(</span>
<span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">30000</span><span class="token punctuation">)</span> <span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">40000</span><span class="token punctuation">)</span> <span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p5 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p6 <span class="token keyword">VALUES</span> LESS THAN <span class="token punctuation">(</span><span class="token number">60000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">PARTITION</span> p7 <span class="token keyword">VALUES</span> LESS THAN MAXVALUE <span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><p><strong>sql查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> p_student <span class="token keyword">where</span> id <span class="token operator">&lt;</span> <span class="token number">30000</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>p_student</td> <td>p0,p1,p2</td> <td>range</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>4</td> <td></td> <td>1</td> <td>100.0</td> <td>Using where</td></tr></tbody></table> <blockquote><p>id小于30000的数据在<code>p0,p1,p2</code>分区中</p></blockquote> <h2 id="_6-type">6.type</h2> <p>type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：</p> <blockquote><p>system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; unique_subquery &gt; index_subquery &gt; range &gt; index_merge &gt; index &gt; ALL</p></blockquote> <p>一般来说，得保证查询至少达到range级别，最好能达到ref</p> <ul><li><strong>system</strong>: 表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index</li> <li><strong>const</strong>: 通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。</li> <li><strong>eq_ref</strong>: 多表关联查询时，被驱动表是<code>主键</code>或<code>唯一索引</code>扫描，对于每个索引键，表中<code>只有一条记录</code>与之匹配，且为非空not null。</li> <li><strong>ref</strong>: 不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。</li> <li><strong>fulltext</strong>：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引</li> <li><strong>ref_or_null</strong>：与ref方法类似，只是增加了null值的比较。实际用的不多。</li> <li><strong>unique_subquery</strong>：用于where中的in形式子查询，子查询返回不重复值唯一值</li> <li><strong>index_subquery</strong>：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。</li> <li><strong>range</strong>：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现<code>between 、&lt; 、&gt; 、in</code> 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。</li> <li><strong>index</strong>：扫描遍历索引树(扫描全表的索引，从索引中获取数据)。常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。</li> <li><strong>ALL</strong>: 全表扫描,然后再在server层进行过滤返回符合要求的记录。从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化。</li></ul> <blockquote><p><a href="https://www.cnblogs.com/nxld/p/6443547.html" target="_blank" rel="noopener noreferrer">参照--mysql explain执行计划详解<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></blockquote> <p><code>type</code>细节比较多，所以单独一节来介绍，具体请查看 <a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-05-explain-type.html">explain之type</a></p> <h2 id="_7-possible-keys">7.possible_keys</h2> <p><strong>possible_keys</strong>: 可能使用到的索引，实际不一定使用<br> <strong>key</strong>: 实际使用的索引，为null表示没有使用索引<br> <strong>key_len</strong>: 索引使用的字节数</p> <p>这三个我们一起来演示</p> <ul><li><strong>possible_keys</strong>有值，<strong>key</strong>才可能有值；<strong>possible_keys</strong>为空，<strong>key</strong>必为空。</li> <li><strong>key</strong>有值，<strong>key_len</strong>有值。<strong>key</strong>为空，<strong>key_len</strong>必为空。</li></ul> <h3 id="_7-1-possible-keys">7-1.possible_keys</h3> <p><strong>使用索引的示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> language_id <span class="token keyword">from</span> film 
<span class="token keyword">where</span> language_id <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><blockquote><p>有where条件，language_id是普通索引，不是全表扫描。会有多条数据返回，所以是ref，而不是const。</p></blockquote> <p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>ref</td> <td>idx_fk_language_id</td> <td>idx_fk_language_id</td> <td>1</td> <td>const</td> <td>1000</td> <td>100.0</td> <td>Using index</td></tr></tbody></table> <p><strong>没有使用索引的示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film 
<span class="token keyword">where</span> language_id <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><blockquote><p>有where条件，，language_id是普通索引，不是全表扫描，查询字段是所有字段，language_id记录很多。
mysql认为全表all扫描处理的效率比通过<code>language_id</code>找到对于的主键索引后，再通过主键索引找到字段的效率高。所以使用了all</p></blockquote> <p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>ALL</td> <td>idx_fk_language_id</td> <td></td> <td></td> <td></td> <td>1000</td> <td>100.0</td> <td>Using where</td></tr></tbody></table> <blockquote><p>即使possible_keys有值，但是<code>key</code>还时为空，即实际也不一定使用到。</p></blockquote> <h3 id="_7-2-key值多条">7-2.key值多条</h3> <p>一般key都为一个值，当type为index_merge时，会出现多个值。</p> <p><strong>查询sql</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film 
<span class="token keyword">where</span> 
film_id <span class="token operator">=</span><span class="token number">10</span> 
<span class="token operator">or</span> title <span class="token operator">=</span><span class="token string">'ACADEMY DINOSAUR'</span> 
<span class="token operator">or</span> original_language_id <span class="token operator">=</span><span class="token number">2</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>由于结果比较长，所以使用列方式打印出来了。</p> <p><strong>执行计划结果</strong></p> <table><thead><tr><th>Name</th> <th>Value</th></tr></thead> <tbody><tr><td>id</td> <td>1</td></tr> <tr><td>select_type</td> <td>SIMPLE</td></tr> <tr><td>table</td> <td>film</td></tr> <tr><td>partitions</td> <td></td></tr> <tr><td>type</td> <td>index_merge</td></tr> <tr><td>possible_keys</td> <td>PRIMARY,idx_title,idx_fk_original_language_id</td></tr> <tr><td>key</td> <td>PRIMARY,idx_title,idx_fk_original_language_id</td></tr> <tr><td>key_len</td> <td>2,514,2</td></tr> <tr><td>ref</td> <td></td></tr> <tr><td>rows</td> <td>3</td></tr> <tr><td>filtered</td> <td>100.0</td></tr> <tr><td>Extra</td> <td>Using union(PRIMARY,idx_title,idx_fk_original_language_id); Using where</td></tr></tbody></table> <h3 id="_7-3-key-len计算">7-3.key_len计算</h3> <p><strong>计算公式</strong></p> <table><thead><tr><th>列类型</th> <th>KEY_LEN</th> <th>备注</th></tr></thead> <tbody><tr><td>int</td> <td><code>key_len = 4+1</code></td> <td>int为4bytes,允许为NULL,加1byte</td></tr> <tr><td>bigint not null</td> <td><code>key_len=8</code></td> <td>bigint为8bytes</td></tr> <tr><td>char(30) utf8</td> <td><code>key_len=30*3+1</code></td> <td>utf8每个字符为3bytes,允许为NULL,加1byte</td></tr> <tr><td>varchar(30) not null utf8</td> <td><code>key_len=30*3+2</code></td> <td>utf8每个字符为3bytes,变长数据类型,加2bytes</td></tr> <tr><td>varchar(30) utf8</td> <td><code>key_len=30*3+2+1</code></td> <td>utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes</td></tr> <tr><td>text(10) utf8</td> <td><code>key_len=30*3+2+1</code></td> <td>TEXT截取部分,被视为动态列类型。</td></tr></tbody></table> <blockquote><p>key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by/group by这一部分被选中的索引列的。</p> <p>可以为null的长度需要<code>+1</code>。</p> <p>如果不记得类型长度的可以查看下第一节的<a href="/manor-farm-mysql/zh/guide/advanced/optimization/zh/guide/essentials/e-01-install.html#_5-数据类型">数据类型</a></p></blockquote> <p>我们以film来说明，我们先看下其索引的结构</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> film <span class="token punctuation">(</span>
  film_id <span class="token keyword">SMALLINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  title <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">128</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  description <span class="token keyword">TEXT</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  release_year <span class="token keyword">YEAR</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  language_id <span class="token keyword">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  original_language_id <span class="token keyword">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  rental_duration <span class="token keyword">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">3</span><span class="token punctuation">,</span>
  rental_rate <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">4.99</span><span class="token punctuation">,</span>
  length <span class="token keyword">SMALLINT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  replacement_cost <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">19.99</span><span class="token punctuation">,</span>
  rating <span class="token keyword">ENUM</span><span class="token punctuation">(</span><span class="token string">'G'</span><span class="token punctuation">,</span><span class="token string">'PG'</span><span class="token punctuation">,</span><span class="token string">'PG-13'</span><span class="token punctuation">,</span><span class="token string">'R'</span><span class="token punctuation">,</span><span class="token string">'NC-17'</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'G'</span><span class="token punctuation">,</span>
  special_features <span class="token keyword">SET</span><span class="token punctuation">(</span><span class="token string">'Trailers'</span><span class="token punctuation">,</span><span class="token string">'Commentaries'</span><span class="token punctuation">,</span><span class="token string">'Deleted Scenes'</span><span class="token punctuation">,</span><span class="token string">'Behind the Scenes'</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  last_update <span class="token keyword">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>  <span class="token punctuation">(</span>film_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> idx_title <span class="token punctuation">(</span>title<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> idx_fk_language_id <span class="token punctuation">(</span>language_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> idx_fk_original_language_id <span class="token punctuation">(</span>original_language_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">CONSTRAINT</span> fk_film_language <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>language_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token keyword">language</span> <span class="token punctuation">(</span>language_id<span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">RESTRICT</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span><span class="token punctuation">,</span>
  <span class="token keyword">CONSTRAINT</span> fk_film_language_original <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>original_language_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> <span class="token keyword">language</span> <span class="token punctuation">(</span>language_id<span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">RESTRICT</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br></div></div><p><strong>film里每个索引计算长度结果为</strong></p> <table><thead><tr><th>索引名</th> <th>字段</th> <th>类型</th> <th>长度(字节)</th> <th>备注</th></tr></thead> <tbody><tr><td>PRIMARY KEY</td> <td>film_id</td> <td>SMALLINT</td> <td>2 bytes</td> <td>SMALLINT为 2 bytes,不允许为NULL,不加1byte</td></tr> <tr><td>idx_title</td> <td>title</td> <td>VARCHAR(128)</td> <td><code>128*4+2=514</code> bytes</td> <td>utf8mb4每个字符为4bytes,变长数据类型,加2bytes,不为NULL,不加1byte</td></tr> <tr><td>idx_fk_language_id</td> <td>language_id</td> <td>TINYINT</td> <td>1 byte</td> <td>TINYINT为 1 bytes,不允许为NULL,不加1byte</td></tr> <tr><td>idx_fk_original_language_id</td> <td>original_language_id</td> <td>TINYINT</td> <td>1 byte</td> <td>TINYINT为 1 bytes,不允许为NULL,不加1byte</td></tr></tbody></table> <blockquote><p>我们使用的编码时<code>utf8mb4</code>，而不是<code>utf8</code>。所以一个字符是4个字节。</p></blockquote> <p>下面我们以不同的索引作为条件来查询，查看执行计划结果。</p> <p><strong>以lfilm_id查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> film_id <span class="token keyword">from</span> film 
<span class="token keyword">where</span> film_id <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><blockquote><p><code>key_len</code>的长度为2</p></blockquote> <p><strong>以language_id查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> film_id <span class="token keyword">from</span> film 
<span class="token keyword">where</span> language_id <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><blockquote><p><code>key_len</code>的长度为2</p></blockquote> <p><strong>以title查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> title <span class="token keyword">from</span> film 
<span class="token keyword">where</span> title <span class="token operator">=</span> <span class="token string">'ACADEMY DINOSAUR'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><blockquote><p><code>key_len</code>的长度为514</p> <p><code>128*4+2=514</code> utf8mb4每个字符为4bytes,变长数据类型,加2bytes,不为NULL,不加1byte</p></blockquote> <h2 id="_8-ref">8.ref</h2> <p><strong>ref</strong> 会显示以下几种值</p> <ul><li>如果是使用的常数等值查询，这里会显示const</li> <li>关联查询，被驱动表的ref列，显示驱动表的关联字段</li> <li>如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func</li></ul> <div class="custom-block tip"><p class="custom-block-title">提示</p> <p>条件列上要有索引，如果不是索引的话，那么是all全表，ref为空</p></div> <h3 id="_8-3-常量">8-3.常量</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> film <span class="token keyword">where</span> title <span class="token operator">=</span> <span class="token string">'AFRICAN EGG'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>ref</td> <td>idx_title</td> <td>idx_title</td> <td>514</td> <td>const</td> <td>1</td> <td>100.0</td> <td></td></tr></tbody></table> <blockquote><p><code>title = 'AFRICAN EGG'</code> 其中<code>AFRICAN EGG</code>为常量，且title为索引列，所以ref为const</p></blockquote> <h3 id="_8-2-关联查询">8-2.关联查询</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> film_actor<span class="token punctuation">.</span>actor_id <span class="token punctuation">,</span> film<span class="token punctuation">.</span>title 
<span class="token keyword">from</span> film_actor 
<span class="token keyword">left</span> <span class="token keyword">join</span> film 
<span class="token keyword">on</span> film<span class="token punctuation">.</span>film_id <span class="token operator">=</span> film_actor<span class="token punctuation">.</span>film_id
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>film_actor</td> <td></td> <td>index</td> <td></td> <td>idx_fk_film_id</td> <td>2</td> <td></td> <td>5462</td> <td>100.0</td> <td>Using index</td></tr> <tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>eq_ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td>sakila.film_actor.film_id</td> <td>1</td> <td>100.0</td> <td></td></tr></tbody></table> <blockquote><p>关联查询，被驱动表<code>film</code>的<code>ref</code>列，显示驱动表<code>film_actor</code>的关联字段<code>sakila.film_actor.film_id</code></p></blockquote> <h2 id="_9-rows">9.rows</h2> <p><strong>rows</strong>：这里是执行计划中估算的扫描行数，不是精确值<br> <strong>filtered</strong>: server过滤后数据给客户端数量/引擎层返回给server数据量百分比。
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> film_actor<span class="token punctuation">.</span>actor_id <span class="token punctuation">,</span> film<span class="token punctuation">.</span>title 
<span class="token keyword">from</span> film_actor 
<span class="token keyword">left</span> <span class="token keyword">join</span> film 
<span class="token keyword">on</span> film<span class="token punctuation">.</span>film_id <span class="token operator">=</span> film_actor<span class="token punctuation">.</span>film_id
<span class="token keyword">where</span> film_actor<span class="token punctuation">.</span>actor_id  <span class="token operator">=</span> <span class="token number">1</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><strong>执行计划结果</strong></p> <table><thead><tr><th>id</th> <th>select_type</th> <th>table</th> <th>partitions</th> <th>type</th> <th>possible_keys</th> <th>key</th> <th>key_len</th> <th>ref</th> <th>rows</th> <th>filtered</th> <th>Extra</th></tr></thead> <tbody><tr><td>1</td> <td>SIMPLE</td> <td>film_actor</td> <td></td> <td>ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td>const</td> <td>19</td> <td>100.0</td> <td>Using index</td></tr> <tr><td>1</td> <td>SIMPLE</td> <td>film</td> <td></td> <td>eq_ref</td> <td>PRIMARY</td> <td>PRIMARY</td> <td>2</td> <td>sakila.film_actor.film_id</td> <td>1</td> <td>100.0</td> <td></td></tr></tbody></table> <blockquote><p>通常来说<code>rows</code>越小越好，io消耗越少。</p> <p><code>filtered</code>是百分比，越大越好，说明引擎层给server层的数据都是有用的数据。</p></blockquote> <h2 id="_10-extra">10.extra</h2> <p><strong>extra</strong>: 一些重要的额外信息。这个列可以显示的信息非常多，有几十种，常用的有</p> <ul><li>distinct：在select部分使用了distinc关键字，优化Distinct操作，在找到匹配的第一行记录后，立马停止查找同样的值</li> <li>no tables used：不带from字句的查询或者From dual查询</li> <li>NULL: 查询的列，没有被索引覆盖到。where条件是索引列，查询使用了索引(key有值)，但是查询的字段不在索引中，需要通过<strong>回表</strong>查询才能实现。</li> <li>using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。</li> <li>using where：查询的列未被索引覆盖到，where条件不是索引前导列</li> <li>using where using index: 查询字段和where条件字段在索引中，但是where条件不满足前导列索引</li> <li>using index condtion: 查询字段不在索引中，where条件满足前置索引的范围性查询</li> <li>using temporary: 使用临时表存储中间结果。临时表可以是内存、磁盘临时表</li> <li>using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中</li> <li>使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。</li> <li>using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。</li> <li>using sort_union，using_union，using intersect，using sort_intersection
<ul><li>using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集</li> <li>using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集</li></ul></li> <li>using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。</li> <li>firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个</li> <li>loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个</li></ul> <p>extra细节比较多，所以单独一节来介绍，具体请查看<a href="/manor-farm-mysql/zh/guide/advanced/optimization/a-06-explain-extra.html">explain之extra</a></p></div> <footer class="page-edit" style="display:none;"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2021-1-11 2:58:36 PM</span></div></footer> <!----> <!----> <!----></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div><!----><!----><APlayer audio="" fixed="true" mini="true" theme="#f9bcdd" loop="loop" order="random" preload="auto" volume="0.1" mutex="true" lrc-type="0" list-folded="true" list-max-height="250" storage-name="vuepress-plugin-meting" id="aplayer-fixed"></APlayer></div></div>
    <script src="/manor-farm-mysql/assets/js/app.bbd54b43.js" defer></script><script src="/manor-farm-mysql/assets/js/3.33fa8ee0.js" defer></script><script src="/manor-farm-mysql/assets/js/1.e501e4e7.js" defer></script><script src="/manor-farm-mysql/assets/js/21.4fd4cbc1.js" defer></script><script src="/manor-farm-mysql/assets/js/8.73e907b5.js" defer></script>
  </body>
</html>
